package com.ljxt.dt.dao.purchasingmanagementdao.impl;

import com.ljxt.dt.dao.purchasingmanagementdao.CountProducer;
import com.ljxt.dt.dao.purchasingmanagementdao.PurchaseInfoDao;
import com.ljxt.dt.domain.*;
import com.ljxt.dt.util.DaoUtil;
import com.ljxt.dt.util.xhq.ClassUtil;
import com.ljxt.dt.util.xhq.JdbcUtils;
import com.ljxt.dt.util.xhq.ResultSetConsumer;
import com.ljxt.dt.util.xhq.test.ContinuousTableMapping;
import com.ljxt.dt.vo.purchasingmanagement.CgddHandlerInfo;
import com.ljxt.dt.vo.purchasingmanagement.PurchaseInfoSelect;
import com.ljxt.dt.vo.purchasingmanagement.PurchaseMainVo;

import java.io.Serializable;
import java.sql.Array;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * 采购订单表的映射
 * @author 徐浩钦
 * @version 1.0.0
 * @date 2020/5/11 15:15
 * @copyright 老九学堂T11
 */
public class PurchaseInfoDaoImpl implements Serializable, PurchaseInfoDao {

    @Override
    public List<PurchaseMainVo> selectAll(int pageIndex, int pageSize, Class<?> clazz,  PurchaseInfoSelect select) {
        /**
         * 初始化或者默认显示的采购订单主界面的查询
         */

        // 根据拼接条件 和 分页的数据 进行详细的查询的流程
        String sql = "select pi.*,bs.bill_state_name as 'documents_state' from purchase_info as pi inner JOIN bill_state AS bs ON ( bs.id = pi.state_id ) where order_no in\n" +
                "(select order_no from purchase_detail where goods_id in (\n" +
                "select id from goods_info\n" +
                "where goods_name like ? \n" +
                ")group by order_no) ";

        // 用来存储 ？ 对应的参数
        List<Object> list = new ArrayList<>();
        // 获取物品查询的状态，如果是 null 则拼接默认的 %% 模糊查询
        String goodsName = select.getGoodsName();

        if(goodsName == null){
            list.add("%%");
        }else{
            list.add("%"+goodsName+"%");
        }

        // 时间等其余的一系列查询拼接
        if(select.getMinTime() != null && select.getMaxTime() != null){
            sql += " and create_time > ? and create_time < ?";
            list.add(select.getMinTime());
            list.add(select.getMaxTime());

        }if(select.getOrderNo() != null){
            sql += " and order_no like ? ";
            list.add("%"+select.getOrderNo()+"%");

        }if(select.getStateId() != null){
            sql += " and state_id = ?";
            list.add(select.getStateId());

        }if(select.getSupplierId() != null){
            sql += " and supplier_id = ?";
            list.add(select.getSupplierId());
        }

        // 最后拼接 排序的 sql 语句
        sql += " ORDER BY pi.state_id, pi.id DESC ";

        // 调用自定义的方法 进行查询 内置分页的拼接
        List<PurchaseMainVo> purchaseMainVos = JdbcUtils.selectAll(sql, pageIndex, pageSize, PurchaseMainVo.class, list.toArray());

        // 返回结果
        return purchaseMainVos;
    }


    @Override
    public Integer selectCountByAll(PurchaseInfoSelect select) {
        /**
         * 用来查询采购订单显示个数据总个数
         * 具体流程和上面的方法类似
         */

        String sql = "select count(*)\n" +
                "from purchase_info as pi inner JOIN bill_state AS bs ON ( bs.id = pi.state_id ) where order_no in\n" +
                "(select order_no from purchase_detail where goods_id in (\n" +
                "select id from goods_info\n" +
                "where goods_name like ?  \n" +
                ")group by order_no) ";

        List<Object> list = new ArrayList<>();
        String goodsName = select.getGoodsName();
        if(goodsName == null){
            list.add("%%");
        }else{
            list.add("%"+goodsName+"%");
        }

        if(select.getOrderNo() != null){
            sql += " and order_no like ?";
            list.add("%"+select.getOrderNo()+"%");

        }if(select.getStateId() != null){
            sql += " and state_id = ?";
            list.add(select.getStateId());

        }if(select.getSupplierId() != null){
            sql += " and supplier_id = ?";
            list.add(select.getSupplierId());

        }if(select.getMinTime() != null && select.getMaxTime() != null){
            sql += " and create_time > ? and create_time < ?";
            list.add(select.getMinTime());
            list.add(select.getMaxTime());

        }

        Integer number = null;
        try {
            number = JdbcUtils.executeQuery(DaoUtil.dataSource, new CountProducer(),sql,list.toArray());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return number;
    }

    @Override
    public List<GoodsInfo> selectGoodsByPurchaseId(int id) {
        String sql = "select * from  goods_info where id in (\n" +
                "select goods_id from supplier_goods where supplier_id = ?)";
        return JdbcUtils.selectAll(sql,GoodsInfo.class,id);
    }


    @Override
    public List<PurchaseDetail> selectPurchaseDetailByPurchaseInfo(String ordId) {
        String sql = "select * from purchase_detail where order_no = ?";
        Object[] objects = {ordId};
        return JdbcUtils.selectAll(sql,PurchaseDetail.class,objects);
    }

    @Override
    public Double sumMoney(String rodId) {
        String sql = "select SUM(quantity*price) from purchase_detail where order_no = ?";

        ResultSetConsumer<Double> resultSetConsumer = new ResultSetConsumer<Double>() {
            @Override
            public Double apply(ResultSet rs) throws SQLException {
                return rs.getDouble(1);
            }

            @Override
            public Boolean accept(Double object) {
                return false;
            }
        };
        Double d = 0d;
        try {
             d = JdbcUtils.executeQuery(DaoUtil.dataSource,resultSetConsumer, sql, rodId);

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return d;
    }

    @Override
    public ContinuousTableMapping select(String cgddId) {
        // 运来存放参数的映射 map
        LinkedHashMap<String,Object[]> map = new LinkedHashMap<>();

        // 查询 采购订单，商品id，种类数量，统计数量，金额小计的值
        Object[] obj = {cgddId};

        String sql = "select \n" +
                "order_no as 'cgdd',\n" +
                "goods_id as 'spid',\n" +
                "COUNT(goods_id) as 'zlgs',\n" +
                "SUM(quantity) as 'tjsl',\n" +
                "SUM(quantity*price) as 'jexj'\n" +
                "from \n" +
                "purchase_detail where order_no = ? group by purchase_detail.goods_id";
        map.put(sql,obj);

        // 得到这个对象后在根据集合的商品 id 得到另一个集合 设置对应的
        String sql2 = "select \n" +
                "gi.goods_name as 'cgwp', \n" +
                "gi.id as 'spid', \n" +
                "gi.unit as 'jldw', \n" +
                "pd.attr_id as 'spsx', \n" +
                "pd.quantity as 'cgsl', \n" +
                "pd.price as 'cgdj', \n" +
                "gi.picture as 'picture' \n" +
                "from \n" +
                "goods_info as gi inner join purchase_detail as pd on(pd.goods_id = gi.id) \n" +
                "where \n" +
                "pd.order_no = ? and \n" +
                "pd.goods_id = ?";
        Object[] obj2 = {"cgdd","spid"};
        map.put(sql2,obj2);


        String sql3 = "\n" +
                "select id,category_name from category_info where goods_id = ?";
        Object[] obj3 = {"spid"};
        map.put(sql3,obj3);

        String sql4 = "select attr_name from attr_info where category_id = ?";
        Object[] obj4 = {"id"};
        map.put(sql4,obj4);

        ContinuousTableMapping continuousTableMapping = JdbcUtils.continuousTableQuery(DaoUtil.dataSource,map);


//        System.out.println("开始");
//        System.out.println("getContent = "+ continuousTableMapping.getContent().size());
//        System.out.println("getChildLists = "+ continuousTableMapping.getChildLists().size());
//        System.out.println();
//        for (int i = 0; i < continuousTableMapping.getContent().size(); i++) {
//            System.out.println("------------"+ i +"------------");
//            System.out.println(            continuousTableMapping.getContent().get(i));
//            System.out.println(            continuousTableMapping.getChildLists().get(i));
//        }

        return continuousTableMapping;
    }

    @Override
    public Map<String, String> selectAttrAndCategory(Map<String, String> map) {
        Map<String, String> res = new HashMap<>();
        String sql = " select ci.category_name as \"key\", ai.attr_name as 'value' from category_info as ci \n" +
                " inner join attr_info as ai on (ci.id = ai.category_id) \n" +
                " where ci.id = ? and ai.id = ?";
        for (Map.Entry<String, String> entry : map.entrySet()) {
            Map<String, Object> stringObjectMap = JdbcUtils.executeQuery(DaoUtil.dataSource, sql, entry.getKey(), entry.getValue()).get(0);
            res.put(stringObjectMap.get("key").toString(),stringObjectMap.get("value").toString());
        }
        return res;
    }

    @Override
    public List<CgddHandlerInfo> selectHandlerByCgddId(String cgddId) {
        String sql = "SELECT\n" +
                "\thi.order_no AS 'order_No',\n" +
                "\tht.handler_type_name AS 'type_Name',\n" +
                "\thi.NAME AS 'name',\n" +
                "\thr.result AS 'result',\n" +
                "\thi.time AS 'time',\n" +
                "\thi.remarks AS 'remark' \n" +
                "FROM\n" +
                "\thandler_info AS hi\n" +
                "\tINNER JOIN handler_type AS ht ON ( hi.handler_type_id = ht.id )\n" +
                "\tINNER JOIN handler_result AS hr ON ( hr.id = hi.result_id ) \n" +
                "WHERE\n" +
                "\torder_no = ?";

        List<Map<String, Object>> list = JdbcUtils.executeQuery(DaoUtil.dataSource, sql, cgddId);
//        System.out.println("cgddId = "+cgddId);
//        System.out.println("list = "+list);

        return ClassUtil.listMapToListObj(list,CgddHandlerInfo.class);
    }

    @Override
    public HandlerInfo selectObjByCgdd(String cgdd) {
        String sql = "select * from handler_info  where order_no = ? ORDER BY handler_type_id DESC LIMIT 0,1";
        List<Map<String, Object>> list = JdbcUtils.executeQuery(DaoUtil.dataSource, sql, cgdd);
        List<HandlerInfo> handlerInfos = ClassUtil.listMapToListObj(list, HandlerInfo.class);
//        System.out.println(list);
//        System.out.println(handlerInfos);
        return handlerInfos.size() == 0 ? null : handlerInfos.get(0);
    }

    @Override
    public int insertIntoHandlerInfo(HandlerInfo handlerInfo) {
        Map<String, Object> map = ClassUtil.objInsertToSql("handler_info", handlerInfo);
        String sql = map.get("sql").toString();
        List<Object> list = (List<Object>) map.get("list");
        int i = -1;
        try {
            i = JdbcUtils.executeUpdate(DaoUtil.dataSource, sql, list);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        // 如果受影响的行数 > 0 则联动性变化数据库
        if(i > 0){
            String sql1 = "select * from purchase_info where order_no = ?";
            String ordId = handlerInfo.getOrderNo();
            // 根据 sql 和 主键查询 采购信息的对象
            PurchaseInfo purchaseInfo = ClassUtil.listMapToListObj(JdbcUtils.executeQuery(DaoUtil.dataSource, sql1, ordId), PurchaseInfo.class).get(0);
            PurchaseInfo tempPurchaseInfo = new PurchaseInfo();
            Integer id = handlerInfo.getResultId();
            switch (id){
                case 1:
                    tempPurchaseInfo.setStateId(2);
                    break;
                case 2:
                    tempPurchaseInfo.setStateId(4);
                    break;
                case 3:
                    tempPurchaseInfo.setStateId(3);
                    break;
                case 4:
                    tempPurchaseInfo.setStateId(5);
                    break;
                    default:
                        break;
            };
            Map<String, Object> map1 = ClassUtil.updataObj("Purchase_Info", tempPurchaseInfo, purchaseInfo);
            String sql2 = map1.get("sql").toString();
            List<Object> list1 = (List<Object>) map1.get("list");

            int j = 0;
            try {
                j = JdbcUtils.executeUpdate(DaoUtil.dataSource,sql2,list1);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            System.out.println("insetInto 的受影响行数：" + i);
            System.out.println("updata 的受影响行数：" + j);
        }
        return i;
    }

    @Override
    public int updataPurchaseInfo(PurchaseInfo purchaseInfo) {
        String sql = "update handler_info set handler_type_id = 2,result_id = 2 where order_no = ?";
        // 找出修改对象不为 null 的属性 和 值，并将条件对象的值和属性也取出来作为
        return 0;
    }

    @Override
    public List<BillState> selectBillstateAll() {
        String sql = "select * from bill_state;";
        return ClassUtil.listMapToListObj(JdbcUtils.executeQuery(DaoUtil.dataSource,sql),BillState.class);
    }

    @Override
    public List<SupplierInfo> selectSupplierInfoAll() {
        String sql = "select * from supplier_info;";
        return ClassUtil.listMapToListObj(JdbcUtils.executeQuery(DaoUtil.dataSource,sql),SupplierInfo.class);
    }

    @Override
    public int deletePurchaseInfoAndPurchaseDetail(String... orderNo) {
        if(orderNo == null || orderNo.length == 0){
            return 0;
        }

        String sql = "delete from purchase_info where order_no = ? ";
        String sql2 = "delete from purchase_detail where order_no = ? ";

        int i = 0;
        try {
            i += JdbcUtils.executeUpdate(DaoUtil.dataSource,sql,orderNo);
            i += JdbcUtils.executeUpdate(DaoUtil.dataSource,sql2,orderNo);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    @Override
    public boolean isStateIdDelete(String orderNo) {
        String sql = "select * FROM purchase_info where order_no = ?";
        List<Map<String, Object>> list = JdbcUtils.executeQuery(DaoUtil.dataSource, sql, orderNo);
        List<PurchaseInfo> purchaseInfos = ClassUtil.listMapToListObj(list, PurchaseInfo.class);
        return purchaseInfos.get(0).getStateId() > 1 ? false : true;
    }

    @Override
    public int submitPurchaseInfoAndPurchaseDetail(String... orderNo) {



        return 0;
    }


}
